Creating a [materialised]view from generic data in Oracle/Mysql
Posted
by Andrew White
on Stack Overflow
See other posts from Stack Overflow
or by Andrew White
Published on 2010-06-18T00:51:18Z
Indexed on
2010/06/18
1:03 UTC
Read the original article
Hit count: 280
I have a generic datamodel with 3 tables
CREATE TABLE Properties
(
propertyId int(11) NOT NULL AUTO_INCREMENT,
name varchar(80) NOT NULL
)
CREATE TABLE Customers
(
customerId int(11) NOT NULL AUTO_INCREMENT,
customerName varchar(80) NOT NULL
)
CREATE TABLE PropertyValues
(
propertyId int(11) NOT NULL,
customerId int(11) NOT NULL,
value varchar(80) NOT NULL
)
INSERT INTO Properties VALUES (1, 'Age');
INSERT INTO Properties VALUES (2, 'Weight');
INSERT INTO Customers VALUES (1, 'Bob');
INSERT INTO Customers VALUES (2, 'Tom');
INSERT INTO PropertyValues VALUES (1, 1, '34');
INSERT INTO PropertyValues VALUES (2, 1, '80KG');
INSERT INTO PropertyValues VALUES (1, 2, '24');
INSERT INTO PropertyValues VALUES (2, 2, '53KG');
What I would like to do is create a view that has as columns all the ROWS in Properties and has as rows the entries in Customers. The column values are populated from PropertyValues.
e.g.
customerId Age Weight
1 34 80KG
2 24 53KG
I'm thinking I need a stored procedure to do this and perhaps a materialised view (the entries in the table "Properties" change rarely). Any tips?
© Stack Overflow or respective owner